package com.erp.dao.impl;

import com.erp.bean.Inventory;
import com.erp.dao.InventoryDao;
import com.erp.util.db.DataBaseDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author mtf
 * 库存访问数据实现类
 */
public class InventoryDaoImpl extends DataBaseDao implements InventoryDao {
    @Override
    public List<Inventory> findAll() {
        List<Inventory> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            //创建sql语句
            String sql = "select * from inventory";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Inventory inventory = new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
                list.add(inventory);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public Inventory findByInvId(int invid) {
        Inventory inventory = null;
        try {
            //获取数据库连接对象
            buildConnection();
            //创建sql语句
            String sql = "select * from inventory where invid = ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,invid);
            rs = pstmt.executeQuery();
            while (rs.next()){
                inventory = new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return inventory;
    }

    @Override
    public int add(Inventory inventory) {
        int i = 0;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "insert into inventory(gid,num,entid) values (?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,inventory.getGid());
            pstmt.setInt(2,inventory.getNum());
            pstmt.setInt(3,inventory.getEntid());
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    @Override
    public int update(Inventory inventory) {
        int i = 0 ;
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "update inventory set gid = ? ,num = ? , entid = ? where invid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,inventory.getGid());
            pstmt.setInt(2,inventory.getNum());
            pstmt.setInt(3,inventory.getEntid());
            pstmt.setInt(4,inventory.getInvid());
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return i;
    }

    @Override
    public List<Inventory> findByEntid(int entid) {
        List<Inventory> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select * from inventory where entid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,entid);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Inventory inventory = new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
                list.add(inventory);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public List<Inventory> findByGid(int gid) {
        List<Inventory> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select * from inventory where gid = ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,gid);
            rs = pstmt.executeQuery();
            while (rs.next()){
                Inventory inventory = new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
                list.add(inventory);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
        }
        return list;
    }

    @Override
    public List<Inventory> find(String column, String value) {
        List<Inventory> list = new ArrayList<>();
        try {
            //获取数据库连接对象
            buildConnection();
            String sql = "select * from inventory where " + column + " like ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"%"+value+"%");
            rs = pstmt.executeQuery();
            while (rs.next()){
                Inventory inventory = new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
                list.add(inventory);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return list;
    }

    @Override
    public Inventory findByGidob(int gid,int entid) {
        Inventory inventory = null;
        //获取数据库连接对象
        try {
            buildConnection();
            String sql = "select * from inventory where entid=? and gid=? ";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,entid);
            pstmt.setInt(2,gid);
            rs=pstmt.executeQuery();
            while (rs.next()){
                inventory=new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }
        return inventory;
    }

    @Override
    public List<Inventory> limit(int page) {
        List<Inventory> list=new ArrayList<>();
        Inventory inventory =null;
        try {
            buildConnection();
            String sql="select * from inventory limit ?,?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,(page-1)*8);
            pstmt.setInt(2,8);
            rs=pstmt.executeQuery();
            while (rs.next()){
                inventory=new Inventory(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4));
                list.add(inventory);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            close();
        }

        return list;
    }
}
